﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Exam.Model;
using Portal.Utility;
using Portal.Core;
using Portal.Web.Utility;
using Portal.Database.Service;


namespace Portal.Exam.Bussiness
{
    public class QuestionBll
    {
        static string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }

        public QuestionCls CreateModel(string SiteId, string QuestionId)
        {
            string Query = " select TableQuestion.* from TableQuestion where QuestionId = '" + QuestionId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;

            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);


            QuestionCls tQuestionCls = new QuestionCls();
            tQuestionCls.QuestionId = (string)dtResult.Rows[0]["QuestionId"];
            try
            {
                tQuestionCls.Title = (string)dtResult.Rows[0]["Title"];
            }
            catch { }
            try
            {
                tQuestionCls.Question = (string)dtResult.Rows[0]["Question"];
            }
            catch { }
            try
            {
                tQuestionCls.Type = (string)dtResult.Rows[0]["Type"];
            }
            catch { }
            try
            {
                tQuestionCls.MaxPoint = (int)dtResult.Rows[0]["MaxPoint"];
            }
            catch { }
            try
            {
                tQuestionCls.CorrectOrder = (int)dtResult.Rows[0]["CorrectOrder"];
            }
            catch { }
            tQuestionCls.GroupQuestionId = (string)dtResult.Rows[0]["frkGroupQuestionId"];
            try
            {
                tQuestionCls.Order = (int)dtResult.Rows[0]["Order"];
            }
            catch { }
            try
            {
                tQuestionCls.IsMemory = (int)dtResult.Rows[0]["IsMemory"];
            }
            catch { }
            try
            {
                tQuestionCls.SecondHidden = (int)dtResult.Rows[0]["SecondHidden"];
            }
            catch { }
            try
            {
                tQuestionCls.SecondTime = (int)dtResult.Rows[0]["SecondTime"];
            }
            catch { }

            try
            {
                tQuestionCls.AnswerTip = (string)dtResult.Rows[0]["AnswerTip"];
            }
            catch { }
            try
            {
                tQuestionCls.Level = (int)dtResult.Rows[0]["Level"];
            }
            catch { }
            return tQuestionCls;
        }

        public AnswerCls CreateModelAnswer(string SiteId, string AnswerId)
        {
            string Query = " select TableAnswer.* from TableAnswer where AnswerId= '" + AnswerId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;

            SqlDataReader SqlDataReader = SqlCommand.ExecuteReader();
            if (!SqlDataReader.HasRows)
            {
                throw new Exception("Không tìm thấy bản ghi AnswerId có mã: " + AnswerId);
            }

            SqlDataReader.Read();
            AnswerCls AnswerCls = new AnswerCls();
            AnswerCls.AnswerId = SqlDataReader.GetString(SqlDataReader.GetOrdinal("AnswerId"));
            try
            {
                AnswerCls.AnswerTextFirst = SqlDataReader.GetString(SqlDataReader.GetOrdinal("AnswerTextFirst"));
            }
            catch { }
            try
            {
                AnswerCls.AnswerTextLast = SqlDataReader.GetString(SqlDataReader.GetOrdinal("AnswerTextLast"));
            }
            catch { }
            try
            {
                AnswerCls.IndexOrder = SqlDataReader.GetInt32(SqlDataReader.GetOrdinal("IndexOrder"));
            }
            catch { }
            try
            {
                AnswerCls.CorrectOrder = SqlDataReader.GetInt32(SqlDataReader.GetOrdinal("CorrectOrder"));
            }
            catch { }


            return AnswerCls;
        }


        public DataTable ReadingQuestion(
            string SiteId,
            string GroupQuestionId,
            string SubGroupQuestionName,
            string Type,
            string Level,
            string ExamCollectionId,
            string Keyword)
        {
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = " select QuestionId,Title,MaxPoint,GroupQuestionName=TableGroupQuestion.GroupQuestionName,RootGroupQuestionName=TableGroupQuestionRoot.GroupQuestionName from TableQuestion " +
                           "           left outer join TableGroupQuestion on TableGroupQuestion.GroupQuestionId=frkGroupQuestionId" +
                           "           left outer join TableGroupQuestion as TableGroupQuestionRoot on TableGroupQuestionRoot.GroupQuestionId=TableGroupQuestion.RootGroupQuestionId" +
                           "    where frkGroupQuestionId='" + GroupQuestionId + "' and [Type]='" + Type + "' and Level=" + Level;
            if (Keyword.Trim().Length > 0)
            {
                Query += " and Title like N'%" + Keyword + "%'";
            }
            if (!string.IsNullOrEmpty(ExamCollectionId))
            {
                Query += " and QuestionId not in (select frkExamQuestionId from TableExamCollectionQuestion where frkExamCollectionId='" + ExamCollectionId + "')";
            }
            Query += " order by [EntryDate] DESC,Title";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable SearchQuestionWithTemplateId(
            string SiteId,
            string GroupQuestionId,
            string SubGroupQuestionName,
            string Type,
            string Level,
            string ExamTemplateId,
            string Keyword)
        {
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = " select QuestionId,Title,MaxPoint,GroupQuestionName=TableGroupQuestion.GroupQuestionName,RootGroupQuestionName=TableGroupQuestionRoot.GroupQuestionName from TableQuestion " +
                           "           left outer join TableGroupQuestion on TableGroupQuestion.GroupQuestionId=frkGroupQuestionId" +
                           "           left outer join TableGroupQuestion as TableGroupQuestionRoot on TableGroupQuestionRoot.GroupQuestionId=TableGroupQuestion.RootGroupQuestionId" +
                           "    where TableGroupQuestion.RootGroupQuestionId='" + GroupQuestionId + "' and [Type]='" + Type + "' and Level=" + Level;
            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and Title like N'%" + Keyword + "%'";
            }
            if (!string.IsNullOrEmpty(ExamTemplateId))
            {
                Query += " and QuestionId not in (select frkQuestionId from TableExamTemplateQuestion where frkTemplateId='" + ExamTemplateId + "')";
            }

            if (!string.IsNullOrEmpty(SubGroupQuestionName))
            {
                SubGroupQuestionName = FunctionUtilities.CorrectForSearch(SubGroupQuestionName);
                Query += " and TableGroupQuestion.GroupQuestionName like N'%" + SubGroupQuestionName + "%'";
            }
            Query += " order by [EntryDate] DESC,Title";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable SearchQuestion(
            string SiteId,
            string GroupQuestionId,
            string SubGroupQuestionName,
            string Type,
            string Level,
            string ExamCollectionId,
            string Keyword)
        {
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = " select QuestionId,Title,MaxPoint,GroupQuestionName=TableGroupQuestion.GroupQuestionName,RootGroupQuestionName=TableGroupQuestionRoot.GroupQuestionName from TableQuestion " +
                           "           left outer join TableGroupQuestion on TableGroupQuestion.GroupQuestionId=frkGroupQuestionId" +
                           "           left outer join TableGroupQuestion as TableGroupQuestionRoot on TableGroupQuestionRoot.GroupQuestionId=TableGroupQuestion.RootGroupQuestionId" +
                           "    where TableGroupQuestion.RootGroupQuestionId='" + GroupQuestionId + "' and [Type]='" + Type + "' and Level=" + Level;
            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and Title like N'%" + Keyword + "%'";
            }
            if (!string.IsNullOrEmpty(ExamCollectionId))
            {
                Query += " and QuestionId not in (select frkExamQuestionId from TableExamCollectionQuestion where frkExamCollectionId='" + ExamCollectionId + "')";
            }

            if (!string.IsNullOrEmpty(SubGroupQuestionName))
            {
                SubGroupQuestionName = FunctionUtilities.CorrectForSearch(SubGroupQuestionName);
                Query += " and TableGroupQuestion.GroupQuestionName like N'%" + SubGroupQuestionName + "%'";
            }
            Query += " order by [EntryDate] DESC,Title";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingQuestionForInterview(string SiteId, string ExamCode, string MonthId, string Type, string Keyword)
        {
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select QuestionId,Title,MaxPoint from TableQuestion where frkGroupQuestionId='" + MonthId + "' and [Type]='" + Type + "' and QuestionId not in (select ExamQuestionId from TableExamQuestion where ExamCode='" + ExamCode + "')";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and Title like N'%" + Keyword + "%'";
            }
            Query += " order by [EntryDate] DESC,Title";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingAnswer(string SiteId, string QuestionId)
        {
            DataTable dataTable = new DataTable();
            string Query = "select * from tableAnswer where fk_Question_Id='" + QuestionId + "' order by [IndexOrder]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }


        public string AddYesNoQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string Type,
            string GroupQuestionId,
            string Title,
            int CorrectOrder,
            int MaxPoint,
            int SecondTime,
            bool IsMemory,
            int SecondHidden,
            int Level)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewQuestionId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableQuestion(QuestionId,Title,Type,MaxPoint,CorrectOrder,frkGroupQuestionId,[Order],SecondTime,IsMemory,SecondHidden, Level, AssetCode) values (@QuestionId,@Title,@Type,@MaxPoint,@CorrectOrder,@frkGroupQuestionId,@Order,@SecondTime,@IsMemory,@SecondHidden, @Level, @AssetCode)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = NewQuestionId;

            TempSqlCommand.Parameters.Add("@frkGroupQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkGroupQuestionId"].Value = GroupQuestionId;

            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Title"].Value = Title;

            TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@Type"].Value = Type;


            TempSqlCommand.Parameters.Add("@CorrectOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectOrder"].Value = CorrectOrder;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

            TempSqlCommand.Parameters.Add("@Order", SqlDbType.Int);
            TempSqlCommand.Parameters["@Order"].Value = 0;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

            TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
            TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory ? 1 : 0;

            TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

            TempSqlCommand.Parameters.Add("@Level", SqlDbType.Int);
            TempSqlCommand.Parameters["@Level"].Value = Level;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;




            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewQuestionId;
        }

        public string AddBestAnswerQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string Type,
            string GroupQuestionId,
            string Question,
            int MaxPoint,
            int SecondTime,
            bool IsMemory,
            int SecondHidden,
            int Level)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewQuestionId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableQuestion(QuestionId,Title, Question, Type,MaxPoint,CorrectOrder,frkGroupQuestionId,[Order],SecondTime,IsMemory,SecondHidden, Level, AssetCode) values (@QuestionId,@Title, @Question, @Type, @MaxPoint,@CorrectOrder,@frkGroupQuestionId,@Order,@SecondTime,@IsMemory,@SecondHidden, @Level, @AssetCode)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = NewQuestionId;

            TempSqlCommand.Parameters.Add("@frkGroupQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkGroupQuestionId"].Value = GroupQuestionId;

            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NText);
            TempSqlCommand.Parameters["@Title"].Value = Question;

            TempSqlCommand.Parameters.Add("@Question", SqlDbType.NText);
            TempSqlCommand.Parameters["@Question"].Value = Question;


            TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@Type"].Value = Type;


            TempSqlCommand.Parameters.Add("@CorrectOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectOrder"].Value = -1;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

            TempSqlCommand.Parameters.Add("@Order", SqlDbType.Int);
            TempSqlCommand.Parameters["@Order"].Value = 0;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

            TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
            TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory ? 1 : 0;

            TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

            TempSqlCommand.Parameters.Add("@Level", SqlDbType.Int);
            TempSqlCommand.Parameters["@Level"].Value = Level;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewQuestionId;
        }

        public string AddFreeTextQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string Type,
            string GroupQuestionId,
            string Title,
            int MaxPoint,
            string AnswerTip,
            int SecondTime,
            bool IsMemory,
            int SecondHidden,
            int Level)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewQuestionId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableQuestion(QuestionId,Title,Type,MaxPoint,CorrectOrder,frkGroupQuestionId,[Order],AnswerTip,SecondTime,IsMemory,SecondHidden, Level, AssetCode) values (@QuestionId,@Title,@Type,@MaxPoint,@CorrectOrder,@frkGroupQuestionId,@Order,@AnswerTip,@SecondTime,@IsMemory,@SecondHidden, @Level, @AssetCode)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = NewQuestionId;

            TempSqlCommand.Parameters.Add("@frkGroupQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkGroupQuestionId"].Value = GroupQuestionId;

            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Title"].Value = Title;

            TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@Type"].Value = Type;


            TempSqlCommand.Parameters.Add("@CorrectOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectOrder"].Value = -1;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

            TempSqlCommand.Parameters.Add("@Order", SqlDbType.Int);
            TempSqlCommand.Parameters["@Order"].Value = 0;

            TempSqlCommand.Parameters.Add("@AnswerTip", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTip"].Value = AnswerTip;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

            TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
            TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory ? 1 : 0;

            TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

            TempSqlCommand.Parameters.Add("@Level", SqlDbType.Int);
            TempSqlCommand.Parameters["@Level"].Value = Level;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewQuestionId;
        }

        public string AddMultiChoiceQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string Type,
            string GroupQuestionId,
            string Title,
            int MaxPoint,
            int SecondTime,
            bool IsMemory,
            int SecondHidden,
            int Level)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewQuestionId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableQuestion(QuestionId,Title,Type,MaxPoint,CorrectOrder,frkGroupQuestionId,[Order],SecondTime,IsMemory,SecondHidden, Level, AssetCode) values (@QuestionId,@Title,@Type,@MaxPoint,@CorrectOrder,@frkGroupQuestionId,@Order,@SecondTime,@IsMemory,@SecondHidden, @Level, @AssetCode)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = NewQuestionId;

            TempSqlCommand.Parameters.Add("@frkGroupQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkGroupQuestionId"].Value = GroupQuestionId;

            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Title"].Value = Title;

            TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@Type"].Value = Type;


            TempSqlCommand.Parameters.Add("@CorrectOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectOrder"].Value = -1;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

            TempSqlCommand.Parameters.Add("@Order", SqlDbType.Int);
            TempSqlCommand.Parameters["@Order"].Value = 0;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

            TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
            TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory ? 1 : 0;

            TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

            TempSqlCommand.Parameters.Add("@Level", SqlDbType.Int);
            TempSqlCommand.Parameters["@Level"].Value = Level;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;



            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewQuestionId;
        }


        public void UpdateQuestionYesNo(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string QuestionId,
            string Title,
            int CorrectOrder,
            int MaxPoint,
            int SecondTime,
            bool IsMemory,
            int SecondHidden,
            int Level)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "Update TableQuestion set Level=@Level,Title=@Title,CorrectOrder=@CorrectOrder,MaxPoint=@MaxPoint,SecondTime=@SecondTime,IsMemory=@IsMemory,SecondHidden=@SecondHidden where QuestionId=@QuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Title"].Value = Title;

            TempSqlCommand.Parameters.Add("@CorrectOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectOrder"].Value = CorrectOrder;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

            TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
            TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory ? 1 : 0;

            TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

            TempSqlCommand.Parameters.Add("@Level", SqlDbType.Int);
            TempSqlCommand.Parameters["@Level"].Value = Level;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void UpdateQuestionFreeText(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string QuestionId,
            string Title,
            int MaxPoint,
            int SecondTime,
            bool IsMemory,
            int SecondHidden,
            int Level)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "Update TableQuestion set Level=@Level, Title=@Title,CorrectOrder=@CorrectOrder,MaxPoint=@MaxPoint,SecondTime=@SecondTime,IsMemory=@IsMemory,SecondHidden=@SecondHidden where QuestionId=@QuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Title"].Value = Title;

            TempSqlCommand.Parameters.Add("@CorrectOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectOrder"].Value = -1;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

            TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
            TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory ? 1 : 0;

            TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

            TempSqlCommand.Parameters.Add("@Level", SqlDbType.Int);
            TempSqlCommand.Parameters["@Level"].Value = Level;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void AddNewAnswerItem(SqlTransaction _SqlTransaction,
            string SiteId,
            string QuestionId,
            string NewAnswer)
        {
            DataTable dtAns = ReadingAnswer(SiteId, QuestionId);
            int IndexOrder = dtAns.Rows.Count + 1;
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "Insert into TableAnswer(AnswerId,fk_Question_Id,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer) values (@AnswerId,@fk_Question_Id,@AnswerTextFirst,@AnswerTextLast,@IndexOrder,@CorrectAnswer)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@AnswerId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerId"].Value = System.Guid.NewGuid().ToString();

            TempSqlCommand.Parameters.Add("@fk_Question_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_Question_Id"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@AnswerTextFirst", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTextFirst"].Value = NewAnswer;

            TempSqlCommand.Parameters.Add("@AnswerTextLast", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTextLast"].Value = "";

            TempSqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@IndexOrder"].Value = IndexOrder;

            TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@CorrectAnswer"].Value = "";

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void AddNewAnswerCrossLinkItem(
           SqlTransaction _SqlTransaction,
           string SiteId,
           string QuestionId,
           string NewAnswerPA1,
            string NewAnswerPA2)
        {
            DataTable dtAns = ReadingAnswer(SiteId, QuestionId);
            int IndexOrder = dtAns.Rows.Count + 1;
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "Insert into TableAnswer(AnswerId,fk_Question_Id,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer) values (@AnswerId,@fk_Question_Id,@AnswerTextFirst,@AnswerTextLast,@IndexOrder,@CorrectAnswer)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@AnswerId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerId"].Value = System.Guid.NewGuid().ToString();

            TempSqlCommand.Parameters.Add("@fk_Question_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_Question_Id"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@AnswerTextFirst", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTextFirst"].Value = NewAnswerPA1;

            TempSqlCommand.Parameters.Add("@AnswerTextLast", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTextLast"].Value = NewAnswerPA2;

            TempSqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@IndexOrder"].Value = IndexOrder;

            TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@CorrectAnswer"].Value = "YES";

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void AddNewAnswerImageItem(
           SqlTransaction _SqlTransaction,
           string SiteId,
           string QuestionId,
           string NewAnswerPA1,
            string NewAnswerPA2,
            string ImageName)
        {
            DataTable dtAns = ReadingAnswer(SiteId, QuestionId);
            int IndexOrder = dtAns.Rows.Count + 1;
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "Insert into TableAnswer(AnswerId,fk_Question_Id,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer) values (@AnswerId,@fk_Question_Id,@AnswerTextFirst,@AnswerTextLast,@IndexOrder,@CorrectAnswer)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@AnswerId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerId"].Value = System.Guid.NewGuid().ToString();

            TempSqlCommand.Parameters.Add("@fk_Question_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_Question_Id"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@AnswerTextFirst", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTextFirst"].Value = NewAnswerPA1;

            TempSqlCommand.Parameters.Add("@AnswerTextLast", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTextLast"].Value = NewAnswerPA2;

            TempSqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@IndexOrder"].Value = IndexOrder;

            TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@CorrectAnswer"].Value = ImageName;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void UpdateAnswerImageItem(
           SqlTransaction _SqlTransaction,
           string SiteId,
           string QuestionId,
            string ImageName)
        {
            DataTable dtAns = ReadingAnswer(SiteId, QuestionId);

            if (dtAns.Rows.Count > 0)
            {
                SqlConnection SqlConnection;
                bool HasParentTransaction = false;

                if (_SqlTransaction == null)
                {
                    SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                    SqlConnection.Open();
                    _SqlTransaction = SqlConnection.BeginTransaction();
                }
                else
                {
                    HasParentTransaction = true;
                }


                string Query = " Update TableAnswer set CorrectAnswer=@CorrectAnswer where fk_Question_Id=@fk_Question_Id ";
                SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
                TempSqlCommand.Transaction = _SqlTransaction;
                TempSqlCommand.CommandText = Query;                

                TempSqlCommand.Parameters.Add("@fk_Question_Id", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@fk_Question_Id"].Value = QuestionId;                

                TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@CorrectAnswer"].Value = ImageName;

                try
                {
                    TempSqlCommand.ExecuteNonQuery();
                    if (HasParentTransaction == false)
                    {
                        _SqlTransaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    if (HasParentTransaction == false)
                    {
                        _SqlTransaction.Rollback();
                    }
                    throw (ex);
                }

                if (HasParentTransaction == false)
                {
                    TempSqlCommand.Connection.Close();
                }
            }
        }


        public void SaveAnswerItem(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string AnswerId,
            string AnsText)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = " Update TableAnswer set AnswerTextFirst=@AnswerTextFirst where AnswerId=@AnswerId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@AnswerId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerId"].Value = AnswerId;

            TempSqlCommand.Parameters.Add("@AnswerTextFirst", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTextFirst"].Value = AnsText;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void SetRightAnswerItem(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string QuestionId,
            string AnswerId,
            bool Reset)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "";
            if (Reset) Query += " Update TableAnswer set CorrectAnswer='NO' where fk_Question_Id=@QuestionId;";
            Query += " Update TableAnswer set CorrectAnswer='YES' where AnswerId=@AnswerId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@AnswerId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AnswerId"].Value = AnswerId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void SetWrongAnswerItem(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string QuestionId,
            string AnswerId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = " Update TableAnswer set CorrectAnswer='NO' where AnswerId=@AnswerId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@AnswerId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AnswerId"].Value = AnswerId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void SaveQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string QuestionId,
            string Title,
            string Question,
            int MaxPoint,
            string AnswerTip,
            int SecondTime,
            bool IsMemory,
            int SecondHidden,
            int Level)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = " Update TableQuestion set Level=@Level, Title=@Title,Question=@Question, MaxPoint=@MaxPoint,SecondTime=@SecondTime,AnswerTip=@AnswerTip,IsMemory=@IsMemory,SecondHidden=@SecondHidden where QuestionId=@QuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Title"].Value = Title;


            TempSqlCommand.Parameters.Add("@Question", SqlDbType.NText);
            TempSqlCommand.Parameters["@Question"].Value = Question;


            TempSqlCommand.Parameters.Add("@AnswerTip", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerTip"].Value = AnswerTip;


            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

            TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
            TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory ? 1 : 0;

            TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

            TempSqlCommand.Parameters.Add("@Level", SqlDbType.Int);
            TempSqlCommand.Parameters["@Level"].Value = Level;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void DeleteAnswerItem(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string AnswerId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "Delete from TableAnswer where AnswerId=@AnswerId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@AnswerId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@AnswerId"].Value = AnswerId;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void DeleteQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string QuestionId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Delete TableAnswer where fk_Question_Id=@QuestionId;" +
                           " Delete TableQuestion where QuestionId=@QuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }



        public void MoveQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string GroupQuestionId,
            string QuestionId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Update TableQuestion set frkGroupQuestionId=@GroupQuestionId where QuestionId=@QuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@GroupQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@GroupQuestionId"].Value = GroupQuestionId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void SaveQuestionText(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string QuestionId,
            string QuestionText)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Update TableQuestion set Question=@Question where QuestionId=@QuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@Question", SqlDbType.NText);
            TempSqlCommand.Parameters["@Question"].Value = QuestionText;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }



        public void SavePreviewBeforeExamConfig(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string PreviewBeforeExam)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Update TableConfig set PreviewBeforeExam=@PreviewBeforeExam";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;

            TempSqlCommand.Parameters.Add("@PreviewBeforeExam", SqlDbType.NText);
            TempSqlCommand.Parameters["@PreviewBeforeExam"].Value = PreviewBeforeExam;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void AddQuestionToExamCollection(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamCollectionId,
            string ExamQuestionId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Insert into TableExamCollectionQuestion(ExamCollectionQuestionId, frkExamCollectionId, frkExamQuestionId, AssetCode) values (@ExamCollectionQuestionId, @frkExamCollectionId, @frkExamQuestionId, @AssetCode)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;

            string ExamCollectionQuestionId = System.Guid.NewGuid().ToString();

            TempSqlCommand.Parameters.Add("@ExamCollectionQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamCollectionQuestionId"].Value = ExamCollectionQuestionId;

            TempSqlCommand.Parameters.Add("@frkExamCollectionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkExamCollectionId"].Value = ExamCollectionId;

            TempSqlCommand.Parameters.Add("@frkExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = AssetCode;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void RemoveQuestionFromExamTemp(string SiteId, string ExamTemplateId, string QuestionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query = " Delete from TableExamTemplateQuestion " +
                           "    where frkTemplateId='" + ExamTemplateId + "' and frkQuestionId='" + QuestionId + "'";

            DBService.ExecuteNonQuery(null, Query, null);
        }

        public void AddQuestionToExamTemplate(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateId,
            string ExamQuestionId,
            string LevelQuestion)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = string.Empty;
            if (!string.IsNullOrEmpty(LevelQuestion))
            {
                Query = " Insert into TableExamTemplateQuestion(ExamTemplateQuestionId, frkTemplateId, frkQuestionId, AssetCode,LevelQuestion) values (@ExamTemplateQuestionId, @frkTemplateId, @frkQuestionId, @AssetCode,@LevelQuestion)";
            }
            else {
                Query = " Insert into TableExamTemplateQuestion(ExamTemplateQuestionId, frkTemplateId, frkQuestionId, AssetCode,LevelQuestion) values (@ExamTemplateQuestionId, @frkTemplateId, @frkQuestionId, @AssetCode,(select Level from TableQuestion where QuestionId= '" + ExamQuestionId + "' ))";
            }
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;

            string ExamCollectionQuestionId = System.Guid.NewGuid().ToString();

            TempSqlCommand.Parameters.Add("@ExamTemplateQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamTemplateQuestionId"].Value = ExamCollectionQuestionId;

            TempSqlCommand.Parameters.Add("@frkTemplateId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkTemplateId"].Value = ExamTemplateId;

            TempSqlCommand.Parameters.Add("@frkQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = AssetCode;

            if (!string.IsNullOrEmpty(LevelQuestion))
            {
                TempSqlCommand.Parameters.Add("@LevelQuestion", SqlDbType.Int);
                TempSqlCommand.Parameters["@LevelQuestion"].Value = int.Parse(LevelQuestion);
            }

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }




        public string AddQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string GroupQuestionId,
            string Type,
            int Level,
            QuestionCls OQuestion,
            int Overwrite)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            if (string.IsNullOrEmpty(OQuestion.QuestionId))
            {
                OQuestion.QuestionId = System.Guid.NewGuid().ToString();
            }
            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "";
            if (Overwrite == 1)
            {
                Query +=
                    "  Delete from tableanswer where fk_Question_Id=@QuestionId; " +
                    "  Delete from tableQuestion where QuestionId=@QuestionId; ";
            }
            Query +=
                " Insert into TableQuestion(QuestionId,Title,Type,MaxPoint,CorrectOrder,frkGroupQuestionId,[Order],SecondTime,IsMemory,SecondHidden, Level, AssetCode) values (@QuestionId,@Title,@Type,@MaxPoint,@CorrectOrder,@frkGroupQuestionId,@Order,@SecondTime,@IsMemory,@SecondHidden, @Level, @AssetCode)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@QuestionId"].Value = OQuestion.QuestionId;

            TempSqlCommand.Parameters.Add("@frkGroupQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkGroupQuestionId"].Value = GroupQuestionId;

            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Title"].Value = OQuestion.Title;

            TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@Type"].Value = Type;


            TempSqlCommand.Parameters.Add("@CorrectOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectOrder"].Value = OQuestion.CorrectOrder;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = 1;

            TempSqlCommand.Parameters.Add("@Order", SqlDbType.Int);
            TempSqlCommand.Parameters["@Order"].Value = 0;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = 0;

            TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
            TempSqlCommand.Parameters["@IsMemory"].Value = 0;

            TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondHidden"].Value = 0;

            TempSqlCommand.Parameters.Add("@Level", SqlDbType.Int);
            TempSqlCommand.Parameters["@Level"].Value = Level;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                for (int iIndexAnswer = 0; iIndexAnswer < OQuestion.Answers.Count; iIndexAnswer++)
                {
                    TempSqlCommand.Parameters.Clear();
                    Query = "Insert into TableAnswer(AnswerId, fk_Question_Id, AnswerTextFirst, AnswerTextLast, IndexOrder, CorrectAnswer) values (@AnswerId, @fk_Question_Id, @AnswerTextFirst, @AnswerTextLast, @IndexOrder, @CorrectAnswer)";
                    TempSqlCommand.CommandText = Query;

                    TempSqlCommand.Parameters.Add("@AnswerId", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@AnswerId"].Value = OQuestion.Answers[iIndexAnswer].AnswerId;

                    TempSqlCommand.Parameters.Add("@fk_Question_Id", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@fk_Question_Id"].Value = OQuestion.QuestionId;

                    TempSqlCommand.Parameters.Add("@AnswerTextFirst", SqlDbType.NVarChar);
                    TempSqlCommand.Parameters["@AnswerTextFirst"].Value = OQuestion.Answers[iIndexAnswer].AnswerTextFirst;

                    TempSqlCommand.Parameters.Add("@AnswerTextLast", SqlDbType.NVarChar);
                    TempSqlCommand.Parameters["@AnswerTextLast"].Value = OQuestion.Answers[iIndexAnswer].AnswerTextLast;

                    TempSqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                    TempSqlCommand.Parameters["@IndexOrder"].Value = OQuestion.Answers[iIndexAnswer].IndexOrder;

                    TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@CorrectAnswer"].Value = (OQuestion.Answers[iIndexAnswer].CorrectOrder == 1 ? "YES" : "NO");

                    TempSqlCommand.ExecuteNonQuery();
                }
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return OQuestion.QuestionId;
        }

    }
}
